﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HCQ2_IDAL;
using HCQ2_Model;
using HCQ2_Model.VR.Params;
using System.Data;
using HCQ2_Model.APPModel.ParamModel;
using System.Globalization;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRExperienceRankDAL: IT_VRExperienceRankDAL
    {
        /// <summary>
        ///  获取体验排行
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<T_VRExperienceRank> GetPopularityRankData(ExperRecordModel param)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT TOP {0} * FROM (SELECT ROW_NUMBER() OVER(ORDER BY user_id, er_id) AS orderber, * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY user_id,file_id,folder_name ORDER BY score DESC) AS rowber,* FROM T_VRExperienceRank WHERE 1=1 ", param.rows);
            //用户id
            if (param.userId > 0)
                sb.AppendFormat(" AND user_id={0} ", param.userId);
            //用户名
            if (!string.IsNullOrEmpty(param.userName))
                sb.AppendFormat(" AND user_name LIKE '%{0}%' ", param.userName);
            //体验作品
            if (!string.IsNullOrEmpty(param.fileName))
                sb.AppendFormat(" AND file_name LIKE '%{0}%' ", param.fileName);
            //体验项目
            if (!string.IsNullOrEmpty(param.folderName))
                sb.AppendFormat(" AND folder_name LIKE '%{0}%' ", param.folderName);
            sb.AppendFormat(")ranks WHERE ranks.rowber=1)rank WHERE rank.orderber>{0};", (param.page - 1) * param.rows);
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_VRExperienceRank>(dt);
        }
        /// <summary>
        ///  获取体验排行数量
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetPopularityRankCount(ExperRecordModel param)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT COUNT(0) AS num FROM (SELECT ROW_NUMBER() OVER(PARTITION BY user_id,file_id,folder_name ORDER BY score DESC) AS rowber,* FROM T_VRExperienceRank WHERE 1=1 ");
            //用户id
            if (param.userId > 0)
                sb.AppendFormat(" AND user_id={0} ",param.userId);
            //用户名
            if (!string.IsNullOrEmpty(param.userName))
                sb.AppendFormat(" AND user_name LIKE '%{0}%' ", param.userName);
            //体验作品
            if (!string.IsNullOrEmpty(param.fileName))
                sb.AppendFormat(" AND file_name LIKE '%{0}%' ", param.fileName);
            //体验项目
            if (!string.IsNullOrEmpty(param.folderName))
                sb.AppendFormat(" AND folder_name LIKE '%{0}%' ", param.folderName);
            sb.AppendFormat(")ranks WHERE ranks.rowber=1;");
            return HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString()));
        }

        //******************************************接口*******************************************
        /// <summary>
        ///  获取体验排行
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<ExperienceRankModel> GetPopularityRankList(QueryRecordModel model)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT TOP {0} * FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS scroeNber,* FROM (SELECT ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY score DESC,em_time ASC) AS number,er_id,user_id,user_name,file_id,file_name,folder_name,em_time,score,CONVERT(varchar(100),in_date,120) AS in_date,CONVERT(varchar(100),out_date,120) AS out_date FROM dbo.T_VRExperienceRank WHERE 1=1 ", model.size);
            if (model.file_id > 0)
                sb.AppendFormat(@" AND file_id={0} ",model.file_id);
            if (!string.IsNullOrEmpty(model.folder_name))
                sb.AppendFormat(" AND folder_name='{0}' ", model.folder_name);
            //时间
            string inDate = string.Empty, outDate = string.Empty;
            if (!string.IsNullOrEmpty(model.in_date))
                inDate = DateTime.ParseExact(model.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(model.out_date))
                outDate = DateTime.ParseExact(model.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(model.in_date) && !string.IsNullOrEmpty(model.out_date))
                sb.AppendFormat(@" AND in_date>='{0}' AND out_date<='{1}' ", inDate, outDate);
            else if (!string.IsNullOrEmpty(model.in_date) && string.IsNullOrEmpty(model.out_date))
                sb.AppendFormat(@" AND in_date>='{0}' ", inDate);
            else if (string.IsNullOrEmpty(model.in_date) && !string.IsNullOrEmpty(model.out_date))
                sb.AppendFormat(@" AND out_date<='{0}' ", outDate);
            sb.AppendFormat(@")rank1 WHERE rank1.number=1)rank2 WHERE rank2.scroeNber>{0} ORDER BY rank2.score DESC;", (model.page - 1) * model.size);
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<ExperienceRankModel>(dt);
        }

        /// <summary>
        ///  获取体验排行数量
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int GetPopularityRankCount(QueryRecordModel model)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT COUNT(0) AS num FROM (SELECT ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY score DESC,em_time ASC) AS number,* FROM dbo.T_VRExperienceRank WHERE 1=1 ");
            if (model.file_id > 0)
                sb.AppendFormat(@" AND file_id={0} ", model.file_id);
            if (!string.IsNullOrEmpty(model.folder_name))
                sb.AppendFormat(" AND folder_name='{0}' ", model.folder_name);
            sb.AppendFormat(@")rank1 WHERE rank1.number=1;");
            return HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString()));
        }
    }
}
